﻿Public Class Query
    Public sql As String = ""

    Public Sub New()
    End Sub

    Public Sub New(ByRef sql As String)
        Me.sql = sql
    End Sub

    Public Sub setParameter(ByRef name As String, ByRef value As Object)
        If TypeOf value Is String Then
            setString(name, value)
            Return
        End If

        If TypeOf value Is Long Then
            setLong(name, value)
            Return
        End If

        If TypeOf value Is Integer Then
            setInteger(name, value)
            Return
        End If

        If TypeOf value Is Double Then
            setDouble(name, value)
            Return
        End If

        Throw New NotSupportedException()
    End Sub

    Private Sub setString(ByRef name As String, ByRef value As String)
        ' escape [']
        Dim escape As String = value.Replace("'", "''")
        ' setze das escape zwischen ': AAA -> 'AAA'
        escape = "'" & escape & "'"
        replaceEscaped(name, escape)
    End Sub

    Private Sub setLong(ByRef name As String, ByRef value As Long)
        replaceEscaped(name, value.ToString())
    End Sub

    Private Sub setInteger(ByRef name As String, ByRef value As Integer)
        replaceEscaped(name, value.ToString())
    End Sub

    Private Sub setDouble(ByRef name As String, ByRef value As Double)
        replaceEscaped(name, value.ToString)
    End Sub

    Private Sub replaceEscaped(ByRef name As String, ByRef escaped As String)
        Dim search As String = ":" & name
        sql = sql.Replace(search, escaped)
    End Sub

End Class

Public Class DeleteQuery
    Inherits Query

    Sub New(ByRef table As String, ByRef id As Long)
        sql = "delete from " & table & " where id=:id"
        setParameter("id", id)
    End Sub
End Class

Public Class SelectAllQuery
    Inherits Query

    Sub New(ByRef table As String)
        sql = "select * from " & table & " order by id ASC"
    End Sub
End Class

Public Class SelectByIdQuery
    Inherits Query

    Sub New(ByRef table As String, ByRef id As Long)
        sql = "select * from " & table & " where id=:id"
        setParameter("id", id)
    End Sub
End Class
